package com.easyway.android.sql;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;


public class AndroidSQL extends Activity {
	private static String DB_NAME = "mypic.db";
	private static int DB_VERSION = 1;
	private static int NAME;
	private ListView listview;
	private Cursor cursor;
	private SQLiteDatabase db;
	private SQLiteHelper dbHelper;
	private ListAdapter listAdapter;
	
	private EditText etPic;  //picture name area
	private EditText etCode;  // url area
	private Button bt_add;    // add an url
	private Button bt_modify;
	private Button bt_query;   
	
	private List<PicBean> picList = new ArrayList<PicBean>();
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    	super.onCreate(savedInstanceState);
    	setContentView(R.layout.main);
    	
    	etPic = (EditText) findViewById(R.id.etPic);
    	etCode = (EditText) findViewById(R.id.etCode);
    	bt_add = (Button) findViewById(R.id.bt_add);
    	bt_modify = (Button) findViewById(R.id.bt_modify);
    	bt_query = (Button) findViewById(R.id.bt_query);
    	
    	try{
    	
    		dbHelper = new SQLiteHelper(this, DB_NAME, null, DB_VERSION);
    		db = dbHelper.getWritableDatabase();	     	
        	cursor = db.query(SQLiteHelper.TB_NAME, null, null, null, null, null, PicBean.CODE);
        	cursor.moveToFirst();
        	while(!cursor.isAfterLast() && (cursor.getString(1) != null)){    
        		PicBean pic = new PicBean();
        		pic.setId(cursor.getString(0));  // set picture id
        		pic.setPic(cursor.getString(1));  // set picture name
        		pic.setCode(cursor.getString(2));  // set url
        		picList.add(pic);
        		cursor.moveToNext();
        	}
    	}catch(IllegalArgumentException e){
    		e.printStackTrace();
    		++ DB_VERSION;
    		dbHelper.onUpgrade(db, --DB_VERSION, DB_VERSION);
//    		dbHelper.updateColumn(db, SQLiteHelper.ID, "_"+SQLiteHelper.ID, "integer");
    	}
    	listview = (ListView)findViewById(R.id.listView);
    	listAdapter = new ListAdapter();
    	listview.setAdapter(listAdapter);
    	listview.setOnItemClickListener(new ListView.OnItemClickListener(){
		
			public void onItemClick(AdapterView<?> parent, View view, int name,
					long arg3) {
				setSelectedValues(name);
			}    		
    	});
    	
    	
    	
    	//添加图片监听事件
    	bt_add.setOnClickListener(new Button.OnClickListener(){
		
			public void onClick(View arg0) {
				if(etPic.getText().length() > 1 && etCode.getText().length() >1){
					ContentValues values = new ContentValues();
					values.put(PicBean.PIC, etPic.getText().toString().trim());
					values.put(PicBean.CODE, etCode.getText().toString().trim());
				
					Long picID = db.insert(SQLiteHelper.TB_NAME, PicBean.ID, values);
					PicBean pic = new PicBean();
					pic.setId(""+picID);
					pic.setPic(etPic.getText().toString().trim());
					pic.setCode(etCode.getText().toString().trim());
					picList.add(pic);
	        		listview.setAdapter(new ListAdapter());
	        		resetForm();
				}
			}
		});
    	
    	
    // 查询事件监听事件
    	bt_query.setOnClickListener(new Button.OnClickListener(){
    		public void onClick(View view) {
    			picList.removeAll(picList);
    			String sql = null;
    			String sqlPic = etPic.getText().length() > 0 ? PicBean.PIC + " like '%" + etPic.getText().toString().trim() + "%'" : "";    			
    			String sqlCode = etCode.getText().length() > 0 ? PicBean.PIC + " like '%" + etPic.getText().toString().trim() + "%'" : "";
    			if( (!"".equals(sqlPic)) && (!"".equals(sqlCode)) ){
    				sql = sqlPic + " and" + sqlCode;
    			}else if(!"".equals(sqlPic)){
    				sql = sqlPic;
    			}else if(!"".equals(sqlCode)){
    				sql = sqlCode;
    			}
    			cursor = db.query(true, SQLiteHelper.TB_NAME, 
    					new String[]{PicBean.ID, PicBean.PIC, PicBean.CODE}, 
    					sql, 
    					null, null, null, null, null);
    			cursor.moveToFirst();
    			while(!cursor.isAfterLast() && (cursor.getString(1) != null)){  
    				PicBean pic = new PicBean();
    				pic.setId(cursor.getString(0));
    				pic.setPic(cursor.getString(1));
    				pic.setCode(cursor.getString(2));
    				picList.add(pic);
    				cursor.moveToNext();
    			}
    			listview.setAdapter(new ListAdapter());
    			resetForm();
    		}
    	});
    	
 
    	bt_modify.setOnClickListener(new Button.OnClickListener(){
			public void onClick(View arg0) {
				ContentValues values = new ContentValues();
				values.put(PicBean.PIC, etPic.getText().toString().trim());
				values.put(PicBean.CODE, etCode.getText().toString().trim());
				db.update(SQLiteHelper.TB_NAME, values, PicBean.ID + "=" + picList.get(NAME).getId(), null);
				picList.get(NAME).setPic(etPic.getText().toString().trim());
				picList.get(NAME).setCode(etCode.getText().toString().trim());
				listview.setAdapter(new ListAdapter());
				resetForm();
			}
		});  
    }
    
 //设置图像名称
    public void setSelectedValues(int name){
    	NAME = name;
		etPic.setText(picList.get(name).getPic());
		etCode.setText(picList.get(name).getCode());
    }
    

    public void resetForm(){
		etPic.setText("");
		etCode.setText("");
    }
    
    // 关闭数据库
    @Override
    protected void onDestroy() {
    	db.delete(SQLiteHelper.TB_NAME, null, null);
    	super.onDestroy();
    }
    
    private class ListAdapter extends BaseAdapter{
    	public ListAdapter(){
    		super();
    	}
		public int getCount() {
			return picList.size();
		}

		public Object getItem(int name) {
			return name;
		}

		public long getItemId(int name) {
			return name; 
		}

		public View getView(final int name, View view, ViewGroup parent) {
			view = getLayoutInflater().inflate(R.layout.listview, null);
			TextView tv = (TextView) view.findViewById(R.id.tvPic);
			tv.setText("" + picList.get(name).getPic());
			TextView bu = (TextView) view.findViewById(R.id.btRemove);
			bu.setText(R.string.delete);
			bu.setId(Integer.parseInt(picList.get(name).getId()));
			
		
			bu.setOnClickListener(new Button.OnClickListener(){
				public void onClick(View view) {
					try{
						db.delete(SQLiteHelper.TB_NAME, PicBean.ID + "=" + view.getId(), null);
						picList.remove(name);
						listview.setAdapter(new ListAdapter());						
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			});
			return view;
		}
    }
}